We can consume a Kusto database in Fabric from many different places: Notebooks, semantic models, real time dashboards and more. Kusto register all queries sent by the consumers in the query history.

Sometimes, either for logging purpose or to analyze and fix some bug, we need to identify the queries the database is receiving and executing.

Checking the Query History in Kusto Database

The statement is simple:

However, a simple execution may generate an error. The result is too huge, and it will exceed the capacity.

A simple solution is to filter by the query history by execution time:

The Field you are Looking for

Usually what we are looking for is the query text. Most of the times we want to locate the precise text generated by Power BI visuals or applications to confirm if the query is being generated correctly or how to optimize it.

This result comes in the field called Text.

The differences are what queries you would like to find and how do you plan to do it.

• You may be looking for a query which caused an error message
• You may be looking for the most recent queries, after you simulated some test
• You may be looking for the most expensive queries to optimize

Looking for the cause of an error in Query History

We can use the unique Ids in error messages to locate the precise query which caused the error message.

This is the ClientActivityId, which in the error messages appear as ClientRequestId. Filtering by this value we will locate the specific query causing the error.

This field can also be used as a filtering method to discover the queries we are looking for. The starting of the value tells us how the query was generated.

I couldn’t find a full documentation, but this is what I discovered about the ClientActivityId in the query history:

• KPBI: A query generated in Power BI
• KD2RunQuery: I believe this one is from Data Activator
• Kusto.Web.RTA.Dashboards: Real-Time Dashboards
• kustoSynapseSpark: Queries executed from a notebook

You can find queries related to these sources using the expresion startswith or !startswith

Looking for recent Queries

Sometimes we make a test, a simulation from some source, and we want to retrieve the query which arrived in the server.

There are many fields we can use to filter and get the queries generated by our source. Check some interesting ones:

User: The username which sent the query. It’s the user used to authenticate in Kusto. This can vary a lot. Was the query executed interactively or through some resource?

For example, if the query was executed from a report, then it is sent by the semantic model. If it was from Power BI desktop, it may send the authentication of the logged in user. However, from the portal, it will send the authentication specified in the Cloud Connection.

Application: The identification of the application sending the query. The identification may not be so obvious, but it may help.

You can easily check the distinct values existing in these columns and identify which one corresponds to the source of your tests.

Looking for Failed Queries

Sometimes our purpose is to locate the queries causing errors or a specific error

We can use the field State to filter queries according to the final result.

These are possible values for the field State:

• Completed: The query executed successfully
• Failed: The query failed
• Cancelled: The execution was cancelled in the middle

We can filter the queries by the Failed state and use the field FailureReason to identify what was the error.

FailureReason contains a message which already tells us some details about the error, but it’s also a link. If we use CTRL+Click on the link, we will see a popup message about the error.

High Performance Consumption

A common need we may have about query history is to look for queries with high performance consumption.

We can search the queries and order the result in descending orders by one of the fields below and this will give a list of the highest consumption queries being executed.

• Duration
• TotalCPU
• MemoryPeak

The name of the fields already explains what they contain.

We can use Order By or Top, the result is similar:

Deeper Query History Analysis

There are other fields such as CacheStatistics, ScannedExtentsStatistics and ResultSetStatistics which allow us to analyze the query in much deeper detail. I will write more about these in another newsletter.

Summary

Searching Kusto query history is an important step to debug problems and optimize queries